package dao.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import dao.DAOException;
import dao.HolidayDAO;
import model.HolidayDTO;



public class HolidayDAOImpl implements HolidayDAO{
	private static final String dbUrl = "jdbc:mysql://localhost:3306/facilitybookingsystem";
	private static final String dbUserName = "root";
	private static final String dbPassword = "password";
	
	public void insertHoliday(HolidayDTO holiday) throws DAOException {
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String insertSql = "INSERT INTO holiday (HolidayName,HolidayDate) VALUES('" + holiday.getHolidayName()
				+ "', '" + df.format(holiday.getHolidayDate())+"');";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing insert: " + insertSql);
			st.executeUpdate(insertSql);
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}
	public void deleteHoliday(HolidayDTO holiday) throws DAOException {
		
		String deleteSql = "DELETE FROM holiday WHERE HolidayID="
				+ holiday.getHolidayID() + ";";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing delete: " + deleteSql);
			st.executeUpdate(deleteSql);
			st.close();
		} catch (Exception e) {
			String error = "Error deleting users. Nested Exception is: " + e;
			System.out.println("#$#$#@$#@#@$#@$#@$#$#@");
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}
	
	public ArrayList<HolidayDTO> findAllHoliday() throws DAOException {
		ArrayList<HolidayDTO> holidays = new ArrayList<HolidayDTO>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String selectSql = "SELECT * FROM holiday;";
	
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				HolidayDTO oneHoliday= new HolidayDTO();
				oneHoliday.setHolidayID(rs.getInt("HolidayID"));
				oneHoliday.setHolidayName(rs.getString("HolidayName"));
				oneHoliday.setHolidayDate(rs.getDate("HolidayDate"));
				holidays.add(oneHoliday);
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return holidays;
	}
	public HolidayDTO findHoliday(int holidayID) throws DAOException {
		String selectSql = "SELECT * FROM holiday WHERE HolidayID="
				+ holidayID + ";";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		HolidayDTO oneHoliday= new HolidayDTO();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				oneHoliday.setHolidayID(rs.getInt("HolidayID"));
				oneHoliday.setHolidayName(rs.getString("HolidayName"));
				oneHoliday.setHolidayDate(rs.getDate("HolidayDate"));
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return oneHoliday;
	}
}
